package com.example.webdownloadserver.util;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;


 
public class ExcelUtil {
	/** Excel单元格为空（NULL） */
	public static final String EXCEL_CELL_VALUE_NULL = "";
	/** Excel 文件后缀名 - xls */
	public static final String EXCEL_SUFFIX_XLS = ".xls";
	/** Excel 文件后缀名 - xlsx */
	public static final String EXCEL_SUFFIX_XLSX = ".xlsx";
	
    public static List<List<String>> readXlsx(String path) throws IOException {
        InputStream input = new FileInputStream(path);
//        return readXlsx(input);
		return null;
    }
 
    public static List<List<String>> readXls(String path) throws IOException {
        InputStream input = new FileInputStream(path);
        return readXls(input);
    }

    public static List<List<String>> readExcel(String fillName, InputStream input) throws IOException {
    	if(fillName.endsWith("xlsx")){
    		return readXlsx(input);
    	} else {
    		return readXls(input);
    	}
    }
    
    public static List<List<String>> readXlsx(InputStream input) throws IOException {
        List<List<String>> result = new ArrayList<List<String>>();
        XSSFWorkbook workbook = new XSSFWorkbook(input);
        for (Sheet xssfSheet : workbook) {
            if (xssfSheet == null) {
                continue;
            }
            for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
				Row row = xssfSheet.getRow(rowNum);
                int minCellNum = row.getFirstCellNum();
                int maxCellNum = row.getLastCellNum();
                List<String> rowList = new ArrayList<String>();
                for (int i = minCellNum; i < maxCellNum; i++) {
					Cell cell = row.getCell(i);
					if (cell == null) {
                        continue;
                    }
                    String val = getStringVal(cell);
                    if(val==null||"".equals(val.trim())) {
                    	continue;
                    }
                    rowList.add(val);
                }
                if(!rowList.isEmpty()) {
                	result.add(rowList);
				}
            }
        }
        return result;
    }
 
    public static List<List<String>> readXls(InputStream input) throws IOException {
        List<List<String>> result = new ArrayList<List<String>>();
        HSSFWorkbook workbook = new HSSFWorkbook(input);
        for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {
            HSSFSheet sheet = workbook.getSheetAt(numSheet);
            if (sheet == null) {
                continue;
            }
            for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
                HSSFRow row = sheet.getRow(rowNum);
                if(null == row){
                	continue;
				}
                int minCellNum = row.getFirstCellNum();
                int maxCellNum = row.getLastCellNum();
                List<String> rowList = new ArrayList<String>();
                for (int i = minCellNum; i < maxCellNum; i++) {
                    HSSFCell cell = row.getCell(i);
                    if (cell == null) {
                        continue;
                    }
                    String val = getStringVal(cell);
                    if(val==null||"".equals(val.trim())) {
                    	continue;
                    }
                    rowList.add(val);
                }
                if(!rowList.isEmpty()) {
                	result.add(rowList);
				}
            }
        }
        return result;
    }
 
    private static String getStringVal(Cell cell) {
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_BOOLEAN:
                return cell.getBooleanCellValue() ? "TRUE" : "FALSE";
            case Cell.CELL_TYPE_FORMULA:
                return cell.getCellFormula();
            case Cell.CELL_TYPE_NUMERIC:
                cell.setCellType(Cell.CELL_TYPE_STRING);
                return cell.getStringCellValue();
            case Cell.CELL_TYPE_STRING:
                return cell.getStringCellValue();
            default:
                return null;
        }
    }
    
    private static String getStringVal(XSSFCell cell) {
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_BOOLEAN:
                return cell.getBooleanCellValue() ? "TRUE" : "FALSE";
            case Cell.CELL_TYPE_FORMULA:
                return cell.getCellFormula();
            case Cell.CELL_TYPE_NUMERIC:
                cell.setCellType(Cell.CELL_TYPE_STRING);
                return cell.getStringCellValue();
            case Cell.CELL_TYPE_STRING:
                return cell.getStringCellValue();
            default:
                return null;
        }
    }
    
    
	/**
	 * 解析Excel文件获取解析结果。
	 * <p>
	 * 每个Record的记录值集。
	 *
	 */
	public static List<ArrayList<String>> getExcelCellsValue(final MultipartFile excelFile) {
		if (excelFile != null) {
			Sheet sheet = loadExcelSheet(excelFile);
			if (sheet != null && sheet.getLastRowNum() >= 1) {
				List<ArrayList<String>> rowsValues = loadExcelRowsValues(sheet);
				if (rowsValues != null && !rowsValues.isEmpty()) {
					return rowsValues;
				}
			}
		}
		return null;
	}
	
	/**
	 * 根据输入Excel文件解析出Excel文件中第一个sheet页。
	 *
	 * @param
	 * @return Sheet
	 */
	public static Sheet loadExcelSheet(final MultipartFile excelFile) {
		if (excelFile != null && !StringUtils.isEmpty(excelFile.getName())) {
			String fileName = excelFile.getOriginalFilename();
			if (fileName.lastIndexOf(".") != 0 && fileName.lastIndexOf(".") != -1) {
				String suffix = fileName.substring(fileName.lastIndexOf("."));
				if (EXCEL_SUFFIX_XLSX.equalsIgnoreCase(suffix) || EXCEL_SUFFIX_XLS.equalsIgnoreCase(suffix)) {
					FileInputStream fileInputStream = null;
					try {
						// fileInputStream = (FileInputStream)
						Workbook workBook = null;
						if (EXCEL_SUFFIX_XLSX.equalsIgnoreCase(suffix)) {
							workBook = new XSSFWorkbook(excelFile.getInputStream());
						} else {
							workBook = new HSSFWorkbook(excelFile.getInputStream());
						}
						Sheet sheet = workBook.getSheetAt(0);
						return sheet;
					} catch (Exception e) {
						e.printStackTrace();
					} finally {
						if (fileInputStream != null) {
							try {
								fileInputStream.close();
							} catch (IOException e) {
								e.printStackTrace();
							}
						}
					}
				}
			}
		}
		return null;
	}

	/**
	 * 获取对应Excel的Sheet页中所有记录Row中每个Cell的值。
	 * <p>
	 * 
	 * @param
	 * @return List - 记录Row数据集 , ArrayList - 记录Row中Cell数据集
	 */
	public static List<ArrayList<String>> loadExcelRowsValues(final Sheet sheet) {
		/*
		 * Sheet 页中存在非标题数据记录Row
		 */
		if (sheet != null && sheet.getLastRowNum() >= 1) {
			/*
			 * 从非标题行开始解析每一记录Row中数据。
			 */
			/* excel sheet 页中 行值 */
			List<ArrayList<String>> rowsValues = new LinkedList<ArrayList<String>>();
			for (int i = 1; i <= sheet.getLastRowNum(); i++) {
				Row row = sheet.getRow(i);
				/*
				 * 记录Row中存在数据Cell
				 */
				if (row != null && row.getLastCellNum() > 0) {
					/* 单元格值集 */
					ArrayList<String> cellsValues = new ArrayList<String>();
					/*
					 * 逐个字段域解析记录Row中每个字段域中值
					 */
					for (int j = 0; j < row.getLastCellNum(); j++) {
						if (row.getCell(j) != null) {
							row.getCell(j).setCellType(Cell.CELL_TYPE_STRING);
							String cellValue = row.getCell(j).getStringCellValue().trim();
							cellsValues.add(StringUtils.isEmpty(cellValue) ? EXCEL_CELL_VALUE_NULL : cellValue);
						} else {
							cellsValues.add(EXCEL_CELL_VALUE_NULL);
						}
					}
					rowsValues.add(cellsValues);
				}
			}
			return rowsValues;
		}
		return null;
	}
	
	/** 
	 * @Description  导出创建EXCEL
	 * @param list
	 * @param head
	 * @param keys
	 * @param sheetMaxSize
	 * @return
	 * @see
	 */
	public static Workbook exportCreateExcel(List<Map<String, Object>> list, String head, String keys,Integer sheetMaxSize) {
		if(sheetMaxSize==null) {
			sheetMaxSize = 50000;
		}
		SXSSFWorkbook wb = new SXSSFWorkbook(100);
		wb.setCompressTempFiles(true);
		List<String> asList = Arrays.asList(keys.split(","));
		int size = list.size();
		if (size == 0) {
			Sheet sheet1 = wb.createSheet("数据");
			Row createRow1 = sheet1.createRow(0);
			for (int l = 0; l < head.split(",").length; l++) {
				createRow1.createCell(l).setCellValue(head.split(",")[l]);
			}
			return wb;
		}
		int sheetCount = (size + sheetMaxSize - 1) / sheetMaxSize;
		for (int k = 1; k <= sheetCount; k++) {
			String sheetName = "数据" + k;
			Sheet sheet1 = wb.createSheet(sheetName);
			Row createRow1 = sheet1.createRow(0);
			for (int l = 0; l < head.split(",").length; l++) {
				createRow1.createCell(l).setCellValue(head.split(",")[l]);
			}
			int i = sheetMaxSize * (k - 1), row = 1;
			int end = sheetMaxSize * k >= size ? size : sheetMaxSize * k;
			for (; i < end; i++, row++) {
				try {
					createRow1 = sheet1.createRow(row);
					Map<String, Object> map1 = list.get(i);
					for (int j = 0; j < asList.size(); j++) {
						String key = asList.get(j);
						Object valueTemp = map1.get(key.toUpperCase());
						if (valueTemp == null) {
							continue;
						}
						createRow1.createCell(j).setCellValue(valueTemp.toString());
					}
					if (i % 500 == 0) {
						System.out.println(size + sheetName + "createExcel-i=" + i);
					}
				} catch (Exception e) {
					e.printStackTrace();
					System.out.println("createExcel-error-i=" + i + e.getMessage());
				}
			}
		}
		System.out.println("createExcel-end,size=" + size);
		return wb;
	}
}